// This File is part of the "nyaqt" Project
// 
// Copyright  2009 sometmes@gmail.com
// All rights reserved
// 
// This library is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published
// by the Free Software Foundation; either version 2.1 of the License, or
// (at your option) any later version.
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.



using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using nyaqt.Plugins;
using System.Data.Common;

namespace nyaqt.MsSqlClientPlugin
{
    public class ConnectionProperties : Type1ConnectionProperties, nyaqt.Plugins.IConnectionProperties
    {
        public System.Data.IDbConnection Connection
        {
            get
            {
                return this.SqlConnection;
            }
            set
            {
                if (this.SqlConnection != null)
                    this.SqlConnection.InfoMessage -= new SqlInfoMessageEventHandler(SqlConnection_InfoMessage);

                _cn = value as SqlConnection;
                if (value != null)
                {
                    this.SqlConnection.InfoMessage += new SqlInfoMessageEventHandler(SqlConnection_InfoMessage);
                }
            }
        }

        void SqlConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            foreach (SqlError err in e.Errors)
            {
                FireConnectionMessage(err.Message);
            }
        }

        public string Server
        {
            get
            {
                return this.SqlConnection.DataSource;
            }
        }

        public string ServerBrowseKey
        {
            get
            {
                return Server + "/" + UserName;
            }
        }

        public SqlConnection SqlConnection
        {
            get
            {
                return _cn as SqlConnection;
            }
        }

        virtual public List<nyaqt.Plugins.DatabaseEntityInfo> GetSystemDatabases()
        {
            throw new NotImplementedException();
        }

        virtual public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabases()
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "select name from sys.databases";
            cmd.Connection = _cn;
            using (IDataReader r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["name"] as string;
                    e.DisplayName = e.DatabaseName;
                    ret.Add(e);
                }
                r.Close();
            }
            ret.Sort();
            return ret;
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseTables(string database)
        {
            return GetDatabaseTablesEx(database, "sys.tables", false);
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseViews(string database)
        {
            return GetDatabaseTablesEx(database, "sys.views", false);
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseSystemTables(string database)
        {
            return GetDatabaseTablesEx(database, "sys.tables", true);
        }

        public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseSystemViews(string database)
        {
            return GetDatabaseTablesEx(database, "sys.system_views", true);
        }

        private List<DatabaseEntityInfo> GetDatabaseTablesEx(string database, string sysview, bool msShipped)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  select schema_name(schema_id) [schema],name,db_name() db_name,type,is_ms_shipped from " + sysview;
            if (msShipped)
                cmd.CommandText += " where is_ms_shipped=1 or name = 'sysdiagrams'";
            cmd.Connection = _cn;
            using (IDataReader r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["db_name"] as string;
                    e.TableNamespace = r["schema"] as string;
                    e.TableName = r["name"] as string;
                    string type;
                    if (sysview == "sys.tables")
                        type = "TABLE";
                    else
                        type = "VIEW";
                    if (msShipped)
                        type = "SYSTEM " + type;
                    e.TableType = type;
                    e.TableDescription = "";
                    e.DisplayName = string.Format("{0}.{1}", e.TableNamespace, e.TableName);
                    e.ClipboardText1 = e.TableName;
                    e.ClipboardText2 = string.Format("[{0}].[{1}]", e.TableNamespace, e.TableName); ;
                    ret.Add(e);
                }
                r.Close();
            }
            ret.Sort();
            return ret;
        }

        virtual public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseStoredProcedures(string database)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  select db_name() db_name,name,schema_name(schema_id) [schema] from sys.procedures where type = 'P' and name not in ('sp_helpdiagramdefinition','sp_creatediagram','sp_renamediagram','sp_alterdiagram','sp_dropdiagram','sp_upgraddiagrams','sp_helpdiagrams') ";
            cmd.Connection = _cn;
            using (IDataReader r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    DatabaseEntityInfo e = new DatabaseEntityInfo();
                    e.DatabaseName = r["db_name"] as string;
                    e.TableNamespace = r["schema"] as string;
                    e.TableName = r["name"] as string;
                    e.TableType = "STORED PROC";
                    e.TableDescription = "";
                    e.DisplayName = e.TableNamespace + "." + e.TableName;
                    ret.Add(e);
                }
                r.Close();
            }
            ret.Sort();
            return ret;
        }


        virtual public List<nyaqt.Plugins.DatabaseEntityInfo> GetDatabaseSystemStoredProcedures(string database)
        {
            throw new NotImplementedException();
        }

        protected string FormatColumnType(IDataRecord col, bool withMode)
        {
            string type = "";

            object o = col["CHARACTER_MAXIMUM_LENGTH"];
            int char_max_length = 0;
            if (o != DBNull.Value)
                char_max_length = (int)o;

            string type_name = col["DATA_TYPE"] as string;
            bool NULL = "YES".Equals(col["IS_NULLABLE"] as string, StringComparison.CurrentCultureIgnoreCase);
            if (char_max_length == 0)
                type += type_name;
            else if (char_max_length == -1)
                type += type_name + "(max)";
            else
                type += type_name + "(" + char_max_length + ")";

            if (NULL)
                type += " null";
            else
                type += " not null";

            if (withMode)
                type = col["PARAMETER_MODE"] as string + " " + type;

            return type;
        }

        virtual public List<DatabaseEntityInfo> GetDatabaseTableColumns(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            List<string> keys = new List<string>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  select COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c on t.CONSTRAINT_SCHEMA=c.CONSTRAINT_SCHEMA and t.CONSTRAINT_NAME = c.CONSTRAINT_NAME where t.TABLE_SCHEMA = '" + tableNamespace + "' and t.TABLE_NAME = '" + table + "' and CONSTRAINT_TYPE = 'PRIMARY KEY'";
            cmd.Connection = _cn;
            IDataReader r;
            using (r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    keys.Add(r["COLUMN_NAME"] as string);
                }
                r.Close();
            }
            cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '" + tableNamespace + "' and TABLE_NAME = '" + table + "' ORDER BY ORDINAL_POSITION";
            cmd.Connection = _cn;
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["TABLE_CATALOG"] as string;
                e.TableNamespace = r["TABLE_SCHEMA"] as string;
                e.TableName = r["TABLE_NAME"] as string;
                e.ColumnName = r["COLUMN_NAME"] as string;
                e.TableType = "COLUMN";
                e.TableDescription = "";
                if (keys.Contains(e.ColumnName))
                    e.IsPrimaryKey = true;
                e.IsNullable = "YES".Equals(r["IS_NULLABLE"] as string, StringComparison.CurrentCultureIgnoreCase);
                e.DisplayName = e.ColumnName + " : " + FormatColumnType(r, false);
                e.ClipboardText1 = e.ColumnName;
                e.ClipboardText2 = e.ColumnName + "  " + FormatColumnType(r, false);
                ret.Add(e);
            }
            r.Close();
            return ret;
        }

        const string _constraintColumns = @"
select
	db_name() db_name,
	object_schema_name(parent_object_id) fk_schema,
	object_name(parent_object_id) fk_table,
	pc.name fk_column,
	object_name(constraint_object_id) fk_constraint_name,
	rc.name pk_column,
	object_schema_name(referenced_object_id) pk_schema,
	object_name(referenced_object_id) pk_table,
	parent_column_id fk_column_id,
	referenced_column_id pk_column_id
from sys.foreign_key_columns k
join sys.columns pc on k.parent_object_id = pc.object_id and k.parent_column_id = pc.column_id
join sys.columns rc on k.parent_object_id = rc.object_id and k.parent_column_id = rc.column_id
";
        const string _constraints = @"
select distinct
       db_name, fk_schema, fk_table,
       pk_schema, pk_table
from (
" + _constraintColumns + @"
) x
where
    pk_schema = '{0}' and pk_table = '{1}'
    or
    fk_schema = '{0}' and fk_table = '{1}'
";

        public List<DatabaseEntityInfo> GetDatabaseTableRelations(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> list = new List<DatabaseEntityInfo>();
            IDbCommand cmd = null;
            string table_column, tableowner_column, related_column, relatedowner_column, relateddb_column;

            cmd = _cn.CreateCommand();
            //cmd.CommandText = "use " + database + _constraints +" WHERE "
            //    + "pk_schema = '" + tableNamespace + "' and pk_table = '" + table + "' "
            //    + " OR "
            //    + "fk_schema = '" + tableNamespace + "' and fk_table = '" + table + "' ";
            cmd.CommandText = "use " + database + string.Format(_constraints, tableNamespace, table);
            cmd.Connection = _cn;

            IDataReader r = cmd.ExecuteReader();
            bool outgoing;
            while (r.Read())
            {
                if (r["pk_table"] as string == table && r["pk_schema"] as string == tableNamespace)
                {
                    outgoing = false;
                    table_column = "pk_table";
                    tableowner_column = "pk_schema";
                    related_column = "fk_table";
                    relatedowner_column = "fk_schema";
                    relateddb_column = "db_name";
                }
                else
                {
                    outgoing = true;
                    table_column = "fk_table";
                    tableowner_column = "fk_schema";
                    related_column = "pk_table";
                    relatedowner_column = "pk_schema";
                    relateddb_column = "db_name";
                }

                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r[relateddb_column] as string;
                e.TableNamespace = r[tableowner_column] as string;
                e.TableName = r[table_column] as string;
                e.RelatedTable = r[related_column] as string;
                e.RelatedTableNamespace = r[relatedowner_column] as string;
                e.IsOutGoing = outgoing;
                e.TableType = "RELATION";
                e.DisplayName = e.TableNamespace + "." + r[related_column] as string;
                list.Add(e);
            }
            r.Close();
            list.Sort();
            return list;
        }

        virtual public List<DatabaseEntityInfo> GetDatabaseTableRelationColumns(string database, string tableNamespace, string table, string relatedTableNamespace, string relatedTable)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + " SELECT * FROM (" + _constraintColumns + ") x WHERE "
                + "fk_schema = '" + tableNamespace + "' and fk_table = '" + table + "' and "
                + "pk_schema = '" + relatedTableNamespace + "' and pk_table = '" + relatedTable + "' "
                + "order by pk_column_id";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["db_name"] as string;
                e.TableNamespace = r["fk_schema"] as string;
                e.TableName = r["fk_table"] as string;
                e.RelatedTable = r["pk_table"] as string;
                e.RelatedTableNamespace = r["pk_schema"] as string;
                e.ColumnName = r["fk_column"] as string;
                e.RelatedColumnName = r["pk_column"] as string;
                e.TableType = "RELATION COLUMN";
                if (e.IsOutGoing)
                    e.DisplayName = e.RelatedColumnName + " = " + e.ColumnName;
                else
                    e.DisplayName = e.ColumnName + " = " + e.RelatedColumnName;
                ret.Add(e);
            }
            r.Close();
            return ret;
        }

        virtual public List<DatabaseEntityInfo> GetDatabaseStoredProcedureParameters(string database, string tableNamespace, string table)
        {
            List<DatabaseEntityInfo> ret = new List<DatabaseEntityInfo>();
            IDbCommand cmd = _cn.CreateCommand();
            cmd.CommandText = "use " + database + "  select db_name() db_name,* from INFORMATION_SCHEMA.PARAMETERS WHERE  SPECIFIC_SCHEMA = '" + tableNamespace + "' and SPECIFIC_NAME = '" + table + "' ";
            cmd.Connection = _cn;
            IDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                DatabaseEntityInfo e = new DatabaseEntityInfo();
                e.DatabaseName = r["db_name"] as string;
                e.TableNamespace = r["SPECIFIC_SCHEMA"] as string;
                e.TableName = r["SPECIFIC_NAME"] as string;
                e.ColumnName = r["PARAMETER_NAME"] as string;
                e.TableType = "COLUMN";
                e.TableDescription = "";
                e.DisplayName = e.ColumnName + " : " + FormatColumnType(r, true);
                ret.Add(e);
            }
            r.Close();
            return ret;
        }

        public void GetConnectionStringKeywordMap(Dictionary<KnownNyaqtConnectionKeys, string> knownKeywords, Dictionary<string, string> keywordMap)
        {
            knownKeywords[KnownNyaqtConnectionKeys.Server] = "Data Source";
            knownKeywords[KnownNyaqtConnectionKeys.Database] = "Initial Catalog";
            knownKeywords[KnownNyaqtConnectionKeys.Login] = "User ID";
            knownKeywords[KnownNyaqtConnectionKeys.Password] = "Password";

            keywordMap.Add("Application Name", "Application Name");
            keywordMap.Add("Asynchronous Processing", "Asynchronous Processing");
            keywordMap.Add("AttachDbFilename", "AttachDbFilename");
            keywordMap.Add("Connect Timeout", "Connect Timeout");
            keywordMap.Add("Connection Reset", "Connection Reset");
            keywordMap.Add("Context Connection", "Context Connection");
            keywordMap.Add("Current Language", "Current Language");
            keywordMap.Add("Data Source", "Data Source");
            keywordMap.Add("Encrypt", "Encrypt");
            keywordMap.Add("Enlist", "Enlist");
            keywordMap.Add("Failover Partner", "Failover Partner");
            keywordMap.Add("Initial Catalog", "Initial Catalog");
            keywordMap.Add("Integrated Security", "Integrated Security");
            keywordMap.Add("Load Balance Timeout", "Load Balance Timeout");
            keywordMap.Add("MultipleActiveResultSets", "MultipleActiveResultSets");
            keywordMap.Add("Max Pool Size", "Max Pool Size");
            keywordMap.Add("Min Pool Size", "Min Pool Size");
            keywordMap.Add("Network Library", "Network Library");
            keywordMap.Add("Packet Size", "Packet Size");
            keywordMap.Add("Password", "Password");
            keywordMap.Add("Persist Security Info", "Persist Security Info");
            keywordMap.Add("Pooling", "Pooling");
            keywordMap.Add("Replication", "Replication");
            keywordMap.Add("Transaction Binding", "Transaction Binding");
            keywordMap.Add("TrustServerCertificate", "TrustServerCertificate");
            keywordMap.Add("Type System Version", "Type System Version");
            keywordMap.Add("User ID", "User ID");
            keywordMap.Add("User Instance", "User Instance");
            keywordMap.Add("Workstation ID", "Workstation ID");
            keywordMap.Add("app", "Application Name");
            keywordMap.Add("async", "Asynchronous Processing");
            keywordMap.Add("extended properties", "AttachDbFilename");
            keywordMap.Add("initial file name", "AttachDbFilename");
            keywordMap.Add("connection timeout", "Connect Timeout");
            keywordMap.Add("timeout", "Connect Timeout");
            keywordMap.Add("language", "Current Language");
            keywordMap.Add("addr", "Data Source");
            keywordMap.Add("address", "Data Source");
            keywordMap.Add("network address", "Data Source");
            keywordMap.Add("server", "Data Source");
            keywordMap.Add("database", "Initial Catalog");
            keywordMap.Add("trusted_connection", "Integrated Security");
            keywordMap.Add("connection lifetime", "Load Balance Timeout");
            keywordMap.Add("net", "Network Library");
            keywordMap.Add("network", "Network Library");
            keywordMap.Add("pwd", "Password");
            keywordMap.Add("persistsecurityinfo", "Persist Security Info");
            keywordMap.Add("uid", "User ID");
            keywordMap.Add("user", "User ID");
            keywordMap.Add("wsid", "Workstation ID");
        }

        public List<ServerInfo> GetServerList()
        {
            List<ServerInfo> list = new List<ServerInfo>();
            DbDataSourceEnumerator enu = SqlClientFactory.Instance.CreateDataSourceEnumerator();
            DataTable t = enu.GetDataSources();
            foreach (DataRow row in t.Rows)
            {
                ServerInfo item = new ServerInfo();
                item.DisplayName = row["ServerName"] as string;
                if (row["InstanceName"] != DBNull.Value)
                    item.DisplayName += "\\" + row["InstanceName"] as string;
                item.ServerAddress = item.DisplayName;
                list.Add(item);
            }
            return list;
        }

        public bool ServerAddressEqual(string addr1, string addr2)
        {
            return addr1 == addr2;
        }

        public string FormatOwnException(int lineoffset, Exception ex)
        {
            string msg = "";
            if (ex is SqlException)
            {
                SqlException ex2 = ex as SqlException;
                foreach (SqlError err in ex2.Errors)
                {
                    if (msg != "")
                        msg += "\r\n";

                    int line = err.LineNumber;
                    //if (line > 1) line -= 1;
                    msg += "Line:" + (lineoffset + line) + " Number:" + err.Number + " State:" + err.State;
                    if (!string.IsNullOrEmpty(err.Procedure))
                        msg += "Procedure: " + err.Procedure;
                    msg += " ; " + err.Message;
                }
            }
            return msg;
        }
    }
}
































